package nl.saxion.act.db;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import nl.saxion.act.dao.AuctionDAO;
import nl.saxion.act.dao.CatagoryDAO;
import nl.saxion.act.dao.PersonDAO;
import nl.saxion.act.wt.Auction;

public class DatabaseManager {

	private static DatabaseManager uniqueInstance = null;

	private Connection con = null;
	private final String driver = "org.apache.derby.jdbc.EmbeddedDriver";
	private String dbname = null;

	public DatabaseManager(String dbname) {
		this.dbname = dbname;
		if (!dbExists(dbname)) {
			System.err.println("de database bestaat niet....");
		}
		Statement stmt;
		try {
			stmt = con.createStatement();
			ResultSet rs;
			//System.out.println("drop tables");
//			stmt.executeUpdate("DROP TABLE Bid");
//			stmt.executeUpdate("DROP TABLE Auction");
//			stmt.executeUpdate("DROP TABLE Person");
//			stmt.executeUpdate("DROP TABLE Catagory");
			
			 rs = stmt.executeQuery("Select * From Catagory");
			 rs = stmt.executeQuery("Select * From Person");
			 rs = stmt.executeQuery("Select * From Auction");
			 rs = stmt.executeQuery("Select * From Bid");
			 System.out.println("alles geselect");
			 

			rs.close();
		} catch (SQLException e) {
			//e.printStackTrace();
			try {		
				System.out.println("create tables;");
				stmt = con.createStatement();
				stmt.executeUpdate("CREATE table Catagory ( catagoryID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), catagory VARCHAR(55), PRIMARY KEY(catagory))");
				stmt.executeUpdate("CREATE table Person (personID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), name VARCHAR(55), adress VARCHAR(55), city VARCHAR(55), email VARCHAR(55), username VARCHAR(55), password VARCHAR(55), PRIMARY KEY(personID) )");
				stmt.executeUpdate("CREATE table Auction (auctionID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), title VARCHAR(55), description VARCHAR(55), price FLOAT(2), endTime TimeStamp, auctionStarterID INTEGER, catagory VARCHAR(55), PRIMARY KEY(auctionID), foreign key(auctionStarterID) references Person(personID), foreign key(catagory) references Catagory )");
				stmt.executeUpdate("CREATE table Bid (bidID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), bidderID INTEGER, bidPrice FLOAT(2), bidTime TimeStamp, auctionID INTEGER, PRIMARY KEY(bidID), foreign key(bidderID) references Person(personID), foreign key(auctionID) references Auction  )");
					
				stmt.close();
				con.close();
			} catch (SQLException e1) {
				System.out.println("e1");
				e1.printStackTrace();
			}
		}
	}
	
//	public static void main(String args[]) {
//		DatabaseManager db = getInstance("jdbc:derby:C:/Users/Richard/Documents/Nathalie");
//		CatagoryDAO test1 = new CatagoryDAO(db);
//		test1.insertCatagory("Literatuur");
//		test1.insertCatagory("Muziek");
//		test1.insertCatagory("Audio");
//		test1.insertCatagory("Electronica");
//	}

	public static synchronized DatabaseManager getInstance(String dbname) {
		if (uniqueInstance == null) {
			uniqueInstance = new DatabaseManager(dbname);	
		}
		return uniqueInstance;
	}

	private boolean dbExists(String dataSource) {
		boolean exists = false;
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(dataSource + ";create=true");

			exists = true;
		} catch (Exception e) {
			; // Do nothing, as DB does not (yet) exist
		}
		return (exists);
	}

	public void close() {
		try {
			con = DriverManager.getConnection(dbname + ";shutdown=true");
		} catch (SQLException se) {
			; // Do Nothing. System has shut down.
			con = null;
		}
	}

	public Connection getConnection() {
		return con;
	}
}
